﻿namespace AnyOffice.DAL
{
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;

    public class FlowTypeDao
    {
        public int Insert(SqlConnection conn, FlowTypeData data)
        {
            string commandText = "INSERT INTO [dbo].[Flow_Type] ( [FlowSort], [FlowNo], [FlowName], [FormId], [FlowDoc], [FlowType], [ManageUser], [QueryUser], [AutoName], [AutoNum], [AutoLen], [AutoEdit], [QueryItem], [FlowDesc] ) VALUES ( @FlowSort, @FlowNo, @FlowName, @FormId, @FlowDoc, @FlowType, @ManageUser, @QueryUser, @AutoName, @AutoNum, @AutoLen, @AutoEdit, @QueryItem, @FlowDesc ) SELECT SCOPE_IDENTITY() AS RetValue";
            SqlParameter[] commandParameters = new SqlParameter[14];
            commandParameters[0] = new SqlParameter("@FlowSort", SqlDbType.Int);
            commandParameters[0].Value = data.FlowSort;
            commandParameters[1] = new SqlParameter("@FlowNo", SqlDbType.Int);
            commandParameters[1].Value = data.FlowNo;
            commandParameters[2] = new SqlParameter("@FlowName", SqlDbType.NVarChar, 200);
            commandParameters[2].Value = data.FlowName;
            commandParameters[3] = new SqlParameter("@FormId", SqlDbType.Int);
            commandParameters[3].Value = data.FormId;
            commandParameters[4] = new SqlParameter("@FlowDoc", SqlDbType.Bit);
            commandParameters[4].Value = data.FlowDoc;
            commandParameters[5] = new SqlParameter("@FlowType", SqlDbType.Int);
            commandParameters[5].Value = data.FlowType;
            commandParameters[6] = new SqlParameter("@ManageUser", SqlDbType.NText);
            if (data.ManageUserIsNull)
            {
                commandParameters[6].Value = DBNull.Value;
            }
            else
            {
                commandParameters[6].Value = data.ManageUser;
            }
            commandParameters[7] = new SqlParameter("@QueryUser", SqlDbType.NText);
            if (data.QueryUserIsNull)
            {
                commandParameters[7].Value = DBNull.Value;
            }
            else
            {
                commandParameters[7].Value = data.QueryUser;
            }
            commandParameters[8] = new SqlParameter("@AutoName", SqlDbType.NText);
            if (data.AutoNameIsNull)
            {
                commandParameters[8].Value = DBNull.Value;
            }
            else
            {
                commandParameters[8].Value = data.AutoName;
            }
            commandParameters[9] = new SqlParameter("@AutoNum", SqlDbType.Int);
            commandParameters[9].Value = data.AutoNum;
            commandParameters[10] = new SqlParameter("@AutoLen", SqlDbType.Int);
            commandParameters[10].Value = data.AutoLen;
            commandParameters[11] = new SqlParameter("@AutoEdit", SqlDbType.Int);
            commandParameters[11].Value = data.AutoEdit;
            commandParameters[12] = new SqlParameter("@QueryItem", SqlDbType.NText);
            if (data.QueryItemIsNull)
            {
                commandParameters[12].Value = DBNull.Value;
            }
            else
            {
                commandParameters[12].Value = data.QueryItem;
            }
            commandParameters[13] = new SqlParameter("@FlowDesc", SqlDbType.NText);
            if (data.FlowDescIsNull)
            {
                commandParameters[13].Value = DBNull.Value;
            }
            else
            {
                commandParameters[13].Value = data.FlowDesc;
            }
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, commandText, commandParameters);
            if (obj2 != null)
            {
                return Convert.ToInt32(obj2);
            }
            return -1;
        }

        public DataTable SelectAll(SqlConnection conn)
        {
            string commandText = "SELECT * FROM [dbo].[Flow_Type] ORDER BY [FlowSort] ASC";
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText);
            set.Tables[0].TableName = "Flow_Type";
            return set.Tables[0];
        }

        public DataTable SelectAllWithNewPopedom(SqlConnection conn, string userId)
        {
            string commandText = "DECLARE @DeptId nvarchar(20) DECLARE @PrivId nvarchar(20) SELECT @DeptId = CAST([DeptId] AS nvarchar(20)), @PrivId = CAST([PrivId] AS nvarchar(20)) FROM [dbo].[Users] WHERE [UserId] = @UserId SELECT * FROM [dbo].[Flow_Type] WHERE [FlowId] IN ( SELECT DISTINCT [FlowId] FROM [dbo].[Flow_Process] WHERE (CAST([PrcsDept] AS nvarchar(2000)) = 'ALL_DEPT' OR (CHARINDEX(',' + @UserId + ',',',' + ISNULL(CAST([PrcsUser] AS nvarchar(4000)) ,'') + ',') > 0 OR CHARINDEX(',' + @DeptId + ',',',' + ISNULL(CAST([PrcsDept] AS nvarchar(2000)) ,'') + ',') > 0 OR CHARINDEX(',' + @PrivId + ',',',' + ISNULL(CAST([PrcsPriv] AS nvarchar(2000)) ,'') + ',') > 0 )) AND [PrcsId] = 1 ) OR [FlowType] = 2 ORDER BY [FlowNo] ASC, [FlowId] ASC";
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar, 20) };
            commandParameters[0].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText, commandParameters);
            set.Tables[0].TableName = "Flow_Type";
            return set.Tables[0];
        }

        public DataTable SelectByFlowId(SqlConnection conn, int flowId)
        {
            string commandText = "DECLARE @IsUsed bit IF EXISTS(SELECT [RunId] FROM [dbo].[Flow_Run] WHERE [FlowId] = @FlowId) SET @IsUsed = 1 ELSE SET @IsUsed = 0 SELECT *, @IsUsed AS IsUsed FROM [dbo].[Flow_Type] WHERE [FlowId] = @FlowId";
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@FlowId", SqlDbType.Int) };
            commandParameters[0].Value = flowId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText, commandParameters);
            set.Tables[0].TableName = "Flow_Type";
            return set.Tables[0];
        }

        public DataTable SelectByFlowSort(SqlConnection conn, int flowSort)
        {
            string commandText = "SELECT [Flow_Type].* , [Flow_Form_Type].[FormName] FROM [dbo].[Flow_Type], [dbo].[Flow_Form_Type] WHERE [Flow_Type].[FlowSort] = @FlowSort AND [Flow_Type].[FormId] = [Flow_Form_Type].[FormId] ORDER BY [Flow_Type].[FlowNo] ASC, [Flow_Type].[FlowId] ASC";
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@FlowSort", SqlDbType.Int) };
            commandParameters[0].Value = flowSort;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText, commandParameters);
            set.Tables[0].TableName = "Flow_Type";
            return set.Tables[0];
        }

        public DataTable SelectByFormId(SqlConnection conn, int formId)
        {
            string commandText = "SELECT [Flow_Type].[FlowId], [Flow_Type].[FlowName], [Flow_Sort].[SortName] FROM [dbo].[Flow_Type], [dbo].[Flow_Sort] WHERE [Flow_Type].[FormId] = @FormId AND [Flow_Type].[FlowSort] = [Flow_Sort].[SortId] ORDER BY [Flow_Sort].[SortNo] ASC";
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@FormId", SqlDbType.Int) };
            commandParameters[0].Value = formId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText, commandParameters);
            set.Tables[0].TableName = "Flow_Type";
            return set.Tables[0];
        }

        public DataTable SelectByRunId(SqlConnection conn, int runId)
        {
            string commandText = "SELECT [Flow_Type].* FROM [dbo].[Flow_Type], [dbo].[Flow_Run] WHERE [Flow_Run].[RunId] = @RunId AND [Flow_Run].[FlowId] = [Flow_Type].[FlowId]";
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@RunId", SqlDbType.Int) };
            commandParameters[0].Value = runId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText, commandParameters);
            set.Tables[0].TableName = "Flow_Type";
            return set.Tables[0];
        }

        public DataTable SelectQueryByFlowSort(SqlConnection conn, string userId, int flowSort, bool isAdministrator)
        {
            string commandText = "IF @IsAdministrator =1 BEGIN SELECT * FROM [dbo].[Flow_Type] WHERE [FlowSort] = @FlowSort ORDER BY [FlowNo] ASC END ELSE BEGIN DECLARE @DeptId nvarchar(20) DECLARE @PrivId nvarchar(20) SELECT @DeptId = CAST([DeptId] AS nvarchar(20)), @PrivId = CAST([PrivId] AS nvarchar(20)) FROM [dbo].[Users] WHERE [UserId] = @UserId SELECT * FROM [dbo].[Flow_Type] WHERE [FlowSort] = @FlowSort AND ( ([FlowId] IN ( SELECT DISTINCT [FlowId] FROM [dbo].[Flow_Process] WHERE CAST([PrcsDept] AS nvarchar(2000)) = 'ALL_DEPT' OR CHARINDEX(',' + @UserId + ',',',' + ISNULL(CAST([PrcsUser] AS nvarchar(4000)) ,'') + ',') > 0 OR CHARINDEX(',' + @DeptId + ',',',' + ISNULL(CAST([PrcsDept] AS nvarchar(2000)) ,'') + ',') > 0 OR CHARINDEX(',' + @PrivId + ',',',' + ISNULL(CAST([PrcsPriv]AS nvarchar(2000)) ,'') + ',') > 0 ) ) OR [FlowType] = 2 OR ( CHARINDEX(',' + @UserId + ',',',' + ISNULL(CAST([Flow_Type].[ManageUser] AS nvarchar(2000)),'') + ',') > 0 OR CHARINDEX(',' + @UserId + ',',',' + ISNULL(CAST([Flow_Type].[QueryUser] AS nvarchar(2000)),'') + ',') > 0 ) ) ORDER BY [FlowNo] ASC END";
            SqlParameter[] commandParameters = new SqlParameter[3];
            commandParameters[0] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[0].Value = userId;
            commandParameters[1] = new SqlParameter("@FlowSort", SqlDbType.Int);
            commandParameters[1].Value = flowSort;
            commandParameters[2] = new SqlParameter("@IsAdministrator", SqlDbType.Bit);
            commandParameters[2].Value = isAdministrator;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText, commandParameters);
            set.Tables[0].TableName = "Flow_Type";
            return set.Tables[0];
        }

        public bool SetAutoNum(SqlConnection conn, int flowId, int addNum)
        {
            string commandText = "UPDATE [dbo].[Flow_Type] SET [AutoNum] = [AutoNum] + @AddNum WHERE [FlowId] = @FlowId";
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@FlowId", SqlDbType.Int);
            commandParameters[0].Value = flowId;
            commandParameters[1] = new SqlParameter("@AddNum", SqlDbType.Int);
            commandParameters[1].Value = addNum;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandText, commandParameters);
            return true;
        }

        public bool Update(SqlConnection conn, FlowTypeData data)
        {
            string commandText = "UPDATE [dbo].[Flow_Type] SET [FlowSort] = @FlowSort, [FlowNo] = @FlowNo, [FlowName] = @FlowName, [FormId] = @FormId, [FlowDoc] = @FlowDoc, [FlowType] = @FlowType, [AutoName] = @AutoName, [AutoNum] = @AutoNum, [AutoLen] = @AutoLen, [AutoEdit] = @AutoEdit, [FlowDesc] = @FlowDesc WHERE [FlowId] = @FlowId";
            SqlParameter[] commandParameters = new SqlParameter[12];
            commandParameters[0] = new SqlParameter("@FlowId", SqlDbType.Int);
            commandParameters[0].Value = data.FlowId;
            commandParameters[1] = new SqlParameter("@FlowSort", SqlDbType.Int);
            commandParameters[1].Value = data.FlowSort;
            commandParameters[2] = new SqlParameter("@FlowNo", SqlDbType.Int);
            commandParameters[2].Value = data.FlowNo;
            commandParameters[3] = new SqlParameter("@FlowName", SqlDbType.NVarChar, 200);
            commandParameters[3].Value = data.FlowName;
            commandParameters[4] = new SqlParameter("@FormId", SqlDbType.Int);
            commandParameters[4].Value = data.FormId;
            commandParameters[5] = new SqlParameter("@FlowDoc", SqlDbType.Bit);
            commandParameters[5].Value = data.FlowDoc;
            commandParameters[6] = new SqlParameter("@FlowType", SqlDbType.Int);
            commandParameters[6].Value = data.FlowType;
            commandParameters[7] = new SqlParameter("@AutoName", SqlDbType.NText);
            if (data.AutoNameIsNull)
            {
                commandParameters[7].Value = DBNull.Value;
            }
            else
            {
                commandParameters[7].Value = data.AutoName;
            }
            commandParameters[8] = new SqlParameter("@AutoNum", SqlDbType.Int);
            commandParameters[8].Value = data.AutoNum;
            commandParameters[9] = new SqlParameter("@AutoLen", SqlDbType.Int);
            commandParameters[9].Value = data.AutoLen;
            commandParameters[10] = new SqlParameter("@AutoEdit", SqlDbType.Int);
            commandParameters[10].Value = data.AutoEdit;
            commandParameters[11] = new SqlParameter("@FlowDesc", SqlDbType.NText);
            commandParameters[11].Value = data.FlowDesc;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandText, commandParameters);
            return true;
        }

        public bool UpdateManageUser(SqlConnection conn, int flowId, string manageUser)
        {
            string commandText = "UPDATE [dbo].[Flow_Type] SET [ManageUser] = @ManageUser WHERE [FlowId] = @FlowId";
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@FlowId", SqlDbType.Int);
            commandParameters[0].Value = flowId;
            commandParameters[1] = new SqlParameter("@ManageUser", SqlDbType.NText);
            if (manageUser != string.Empty)
            {
                commandParameters[1].Value = manageUser;
            }
            else
            {
                commandParameters[1].Value = DBNull.Value;
            }
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandText, commandParameters);
            return true;
        }

        public bool UpdateQueryItem(SqlConnection conn, int flowId, string queryItem)
        {
            string commandText = "UPDATE [dbo].[Flow_Type] SET [QueryItem] = @QueryItem WHERE [FlowId] = @FlowId";
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@FlowId", SqlDbType.Int);
            commandParameters[0].Value = flowId;
            commandParameters[1] = new SqlParameter("@QueryItem", SqlDbType.NText);
            if (queryItem != string.Empty)
            {
                commandParameters[1].Value = queryItem;
            }
            else
            {
                commandParameters[1].Value = DBNull.Value;
            }
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandText, commandParameters);
            return true;
        }

        public bool UpdateQueryUser(SqlConnection conn, int flowId, string queryUser)
        {
            string commandText = "UPDATE [dbo].[Flow_Type] SET [QueryUser] = @QueryUser WHERE [FlowId] = @FlowId";
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@FlowId", SqlDbType.Int);
            commandParameters[0].Value = flowId;
            commandParameters[1] = new SqlParameter("@QueryUser", SqlDbType.NText);
            if (queryUser != string.Empty)
            {
                commandParameters[1].Value = queryUser;
            }
            else
            {
                commandParameters[1].Value = DBNull.Value;
            }
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandText, commandParameters);
            return true;
        }
    }
}

